package com.luxiu;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;


public class ExcelUtils {
    /**
     * 读取 xlsx
     *
     * @param file File
     * @return 每个 list 包含一行
     * @throws IOException IOException
     */
    public static List<List<String>> readXLSX(File file) throws IOException {
        InputStream inputStream = new FileInputStream(file);
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheetAt(0);

        List<List<String>> list = new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();
        XSSFRow row;
        short lastCellNum;

        for (int i = 0; i <= lastRowNum; i++) {
            row = sheet.getRow(i);
            lastCellNum = row.getLastCellNum();
            List<String> line = new ArrayList<>(lastCellNum);
            row.cellIterator().forEachRemaining(cell -> {
                line.add(cell.toString());
            });
            list.add(line);
        }

        inputStream.close();
        return list;
    }

    /**
     * 读取 xls
     *
     * @param file File
     * @return 每个 list 包含一行
     * @throws IOException IOException
     */
    public static List<List<String>> readXLS(File file) throws IOException {
        InputStream inputStream = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        List<List<String>> list = new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();
        HSSFRow row;
        short lastCellNum;

        for (int i = 0; i <= lastRowNum; i++) {
            row = sheet.getRow(i);
            lastCellNum = row.getLastCellNum();
            List<String> line = new ArrayList<>(lastCellNum);
            row.cellIterator().forEachRemaining(cell -> {
                line.add(cell.toString());
            });
            list.add(line);
        }

        inputStream.close();
        return list;
    }

    /**
     * 写入 xlsx
     *
     * @param file File
     * @param data 每个 list 包含一行
     * @throws IOException IOException
     */
    public static void writeXLSX(File file, List<List<String>> data) throws SecurityException, IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        int rowCount = 0, cellCount = 0;

        for (List<String> line : data) {
            XSSFRow row = sheet.createRow(rowCount++);
            cellCount = 0;
            for (String val : line) {
                XSSFCell cell = row.createCell(cellCount++);
                cell.setCellValue(val);
            }
        }
        workbook.write(new FileOutputStream(file));
        workbook.close();
    }

    /**
     * 写入 xls
     *
     * @param file File
     * @param data 每个 list 包含一行
     * @throws IOException IOException
     */
    public static void writeXLS(File file, List<List<String>> data) throws SecurityException, IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        int rowCount = 0, cellCount = 0;

        for (List<String> line : data) {
            HSSFRow row = sheet.createRow(rowCount++);
            cellCount = 0;
            for (String val : line) {
                HSSFCell cell = row.createCell(cellCount++);
                cell.setCellValue(val);
            }
        }
        workbook.write(new FileOutputStream(file));
        workbook.close();
    }
}